-- Moves the hierarchy branch rooted at the moveHierarchyKey sent in to be the next youngest sibling
-- of the targetHierarchyKey. The item can be moved as the oldest child of the targetHierarchyKey by
-- passing moveAsChild. All descendents are also moved.  Moving means changeing sort order on
-- all to be moved, changing the parent of the one sent in moveHierarchyKey, and possibly changing the depth
-- on all to be moved.
CREATE PROCEDURE [dbo].[asi_HierarchyMoveBranch] @moveHierarchyKey uniqueidentifier, @targetHierarchyKey uniqueidentifier, @addType smallint = 1 AS
DECLARE
    @MoveRootHierarchyKey uniqueidentifier, @MoveDepth int, @MoveLowSort int, @MoveHighSort int,
    @TargetDepth int, @TargetLowSort int, @TargetHighSort int, @TargetRootHierarchyKey uniqueidentifier,
    @TargetParentHierarchyKey uniqueidentifier, @Adjustment int, @MoveAdjustment int, @DepthAdjustment int
BEGIN
    -- 1. Figure out the low and high sort order containing the branch to be moved
    SELECT @MoveRootHierarchyKey = a.RootHierarchyKey, @MoveDepth = a.Depth, @MoveLowSort = a.SortOrder, @MoveHighSort = Min(b.SortOrder) - 1
      FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
            ON a.RootHierarchyKey = b.RootHierarchyKey
           AND a.SortOrder < b.SortOrder
       AND a.Depth >= b.Depth
     WHERE a.HierarchyKey = @moveHierarchyKey
     GROUP BY a.RootHierarchyKey, a.Depth, a.SortOrder

    IF @MoveHighSort IS NULL
        SELECT @MoveHighSort = Max(a.SortOrder)
           FROM Hierarchy a
         WHERE a.RootHierarchyKey = @MoveRootHierarchyKey

    -- 2. Get the SortOrder, Depth and parent of the target and
    -- figure out the low and high sort order of the target area

    -- get the sort order of where the moved item itself should go
    EXEC asi_HierarchyGetAdjacentSortOut @targetHierarchyKey, @addType, @TargetLowSort OUTPUT

    -- then find the relavent keys and the top sort order in the target area
    SELECT @TargetRootHierarchyKey = a.RootHierarchyKey, @TargetDepth = a.Depth,
           @TargetParentHierarchyKey = a.ParentHierarchyKey, @TargetHighSort = Min(b.SortOrder)
      FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
            ON a.RootHierarchyKey = b.RootHierarchyKey
           AND b.SortOrder > @TargetLowSort
     WHERE a.HierarchyKey = @targetHierarchyKey
     GROUP BY a.RootHierarchyKey, a.Depth, a.ParentHierarchyKey
     
     --If we are moving the item as a child of the target, set depth and parent key.
     If @addType = 2 OR @addType = 3
     BEGIN
        SET @TargetParentHierarchyKey = @targetHierarchyKey
        SET @TargetDepth = @TargetDepth + 1
     END

    -- 3. Expand the target area by moving all higher ones back (unless we're moving to the end)
    SET @Adjustment = 0
    IF @TargetHighSort IS NOT NULL AND (@MoveLowSort <> @TargetLowSort OR @MoveRootHierarchyKey <> @TargetRootHierarchyKey)
    BEGIN
        SET @Adjustment = @MoveHighSort - @MoveLowSort + 2048
        UPDATE Hierarchy
           SET SortOrder = SortOrder + @Adjustment
         WHERE SortOrder >= @TargetHighSort
            AND RootHierarchyKey = @TargetRootHierarchyKey
    END

    -- 4. update the SortOrder of the items to be moved so they move into the target area.  Update depth as well
    IF @MoveLowSort <= @TargetLowSort
        SET @Adjustment = 0

    SET @MoveAdjustment = (@TargetLowSort + 2048) - (@MoveLowSort + @Adjustment)
    IF @MoveLowSort = @TargetLowSort
        SET @MoveAdjustment = 0

    SET @DepthAdjustment = @TargetDepth - @MoveDepth

    UPDATE Hierarchy
       SET SortOrder = SortOrder + @MoveAdjustment,
           RootHierarchyKey = @TargetRootHierarchyKey,
           Depth = Depth + @DepthAdjustment
     WHERE RootHierarchyKey = @MoveRootHierarchyKey
       AND SortOrder >= @MoveLowSort + @Adjustment
       AND SortOrder <= @MoveHighSort + @Adjustment

    -- 5. update the moved item to reflect its new parent
    UPDATE Hierarchy
       SET ParentHierarchyKey = @TargetParentHierarchyKey
     WHERE HierarchyKey = @moveHierarchyKey
END

GO